﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.Skins;
using DevExpress.LookAndFeel;
using DevExpress.UserSkins;
using DevExpress.XtraEditors;
using System.Data.OleDb;


namespace 易桥青岛研发中心考勤统计
{
    public partial class Form1 : XtraForm
    {
        static string dir = null;
        static OleDbConnection OleCon = null;
        public Form1()
        {
            InitializeComponent();
            InitGrid();

        }
        BindingList<Person> gridDataList = new BindingList<Person>();
        void InitGrid()
        {
            gridDataList.Add(new Person("John", "Smith"));
            gridDataList.Add(new Person("Gabriel", "Smith"));
            gridDataList.Add(new Person("Ashley", "Smith", "some comment"));
            gridDataList.Add(new Person("Adrian", "Smith", "some comment"));
            gridDataList.Add(new Person("Gabriella", "Smith", "some comment"));
            //gridControl1.DataSource = gridDataList;
        }

        private void simpleButton4_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void simpleButton3_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.DefaultExt = "xls";
            sfd.InitialDirectory = @"d:\";
            sfd.Filter = "excle文件(xls)|*.xls";
            //gridView1.Export(DevExpress.XtraPrinting.ExportTarget.Xlsx,@"c:\1.xlsx");
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                string location = sfd.FileName;
                MessageBox.Show(location, location);
                gridView1.Export(DevExpress.XtraPrinting.ExportTarget.Xls, location);
            }
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
           OpenFileDialog opd = new OpenFileDialog();
           opd.DefaultExt = "mdb";
           opd.InitialDirectory = @"d:\";
           opd.Filter = "数据库文件(mdb)|*.mdb";
           if (opd.ShowDialog() == DialogResult.OK){
               string location = opd.FileName;
               dir = location;
              // MessageBox.Show(dir, dir);
               OleCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+dir);
               //OleCon.Open();
               string cmd = @"SELECT CHECKINOUT.USERID,USERINFO.NAME,min(CHECKTIME) as morning,max(CHECKTIME) as afternoon from CHECKINOUT,USERINFO where CHECKINOUT.USERID = 

USERINFO.USERID group by CHECKINOUT.USERID,USERINFO.NAME,left

(CHECKTIME,10)";
               OleDbDataAdapter odda = new OleDbDataAdapter(cmd,OleCon);
               DataTable dt = new DataTable();
               odda.Fill(dt);
               //MessageBox.Show(dt.Rows.Count.ToString,"");
               this.gridControl1.DataSource = dt;



          // MessageBox.Show("测试","测试");
           }
           
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“dataSet1.DataTable1”中。您可以根据需要移动或删除它。
            //this.dataTable1TableAdapter.Fill(this.dataSet1.DataTable1);
            dateEdit1.DateTime = DateTime.Now;
            dateEdit2.DateTime = DateTime.Now;
        }

        private void simpleButton2_Click(object sender, EventArgs e)
        {
           if(dir == null)
           {
               MessageBox.Show("请先载入文件","错误");
           }
           else{
           // MessageBox.Show(dateEdit1.DateTime.ToString(), dateEdit2.DateTime.ToString());
               string begintime = dateEdit1.DateTime.ToShortDateString() + @" 00:00:00";
               string endtime = dateEdit2.DateTime.ToShortDateString() + @" 23:59:59";
               


                 //  MessageBox.Show(begintime,begintime);

            //string endtime = "2013-3-30 23:59:59";
            OleCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir);
            //OleCon.Open();
            string cmd = @"SELECT CHECKINOUT.USERID,USERINFO.NAME,min(CHECKTIME) as morning,max(CHECKTIME) as afternoon from CHECKINOUT,USERINFO where CHECKINOUT.USERID = USERINFO.USERID and (CHECKTIME between cdate('" + begintime + @"') and cdate('" + endtime + @"')) group by CHECKINOUT.USERID,USERINFO.NAME,left(CHECKTIME,10) order by CHECKINOUT.USERID,min(CHECKTIME)";
            //OleDbCommand sql = new OleDbCommand(cmd,OleCon);
            //sql.Parameters.Add("@begtime",OleDbType.Char,,);
            OleDbDataAdapter odda = new OleDbDataAdapter(cmd, OleCon);
            DataTable dt = new DataTable();
            odda.Fill(dt);
            //MessageBox.Show(dt.Rows.Count.ToString,"");
            this.gridControl1.DataSource = dt;
           }
        }

        private void gridControl1_Click(object sender, EventArgs e)
        {

        }

    }
}